<?php
/**
 * 通勤・通学時間検索用関数<br />
 *
 * @package apamanshop
 * @author　
 * @copyright　
 */
class Search_Model_Seltsukintsugakujikan extends Apm_Model_Base{

	/**
	* 駅候補取得
	*
	* パラメータの駅名からヴァルデータ取得
	*
	* @access	public
	* @param	array パラメータ
	* @return	array 対象項目データ
	*/
	public function getTargetStation($params){

		$getData = array();

		// 環境定数取得
		$configObject = Zend_Registry::get('config');
		$config = $configObject->apm->toArray();

		$client = new Zend_Http_Client("http://apaman.ekiworld.net/exp.cgi");
		$client->setMethod(Zend_Http_Client::POST);

		$client->setParameterPost( array(	"val_htmb"=> "ams_range",
										 	"val_from"=> $params['TgtStNm'],
											"val_range_start" => "0",
											"val_range_end"=> "90",
											"val_type"=> $params['SelStKbn'],
											"val_transfer_count"=> $params['TransferCount']));
		$response = $client->request();

		if ($response->isError()) {
			$getData[0] = "0";
		}else{
			$getData = explode(",",$response->getBody());
		}

		return $getData;
	}

	/**
	* 沿線データ取得
	*
	* パラメータから沿線を取得
	*
	* @access	public
	* @param	array パラメータ
	* @param	array ヴァル研究所データ
	* @return	array 沿線データ
	*/
	public function getEnsenData($params,$valData){

		$db_params = array();
		// 沿線取得SQL作成
		$sql = $this->generateEnsenSelectSql($params,$valData,&$db_params);

		return $this->dbGetList($sql,$db_params);
	}

	/**
	* T94COMMUTING_EKI_T03SUMへのデータインサート処理
	*
	* 集計対象駅の場合,T94COMMUTING_EKI_T03SUMへデータインサート
	*
	* @access	public
	* @param	array パラメータ
	* @param	array ヴァル研究所データ
	* @return	array 対象項目データ
	*/
	public function insertEkiT94($params,$valData){
		$db_params = array();

		// 集計対象判定SQL
		$sql = $this->generateSql($params,$db_params);
		$data = $this->dbGetData($sql,$db_params);

		// 集計対象の場合
		if($data['CNT'] == 0){

			$db = $this->getDbConnection();
			//トランザクションStart
			$this->startTransaction($db);

			try {
				// T94COMMUTING_EKI_T03SUMへデータインサート
				$sql = $this->generateInsertSql($params,$valData);
				$this->dbExecQuery($sql, array() ,$db);
				// コミット
				$this->commitTransaction($db);

			}catch (Exception $e) {

				//ロールバック
				$this->rollbackTransaction($db);
				//DBのエラーメッセージをログに書き出す
				__log_debug(__FILE__, __LINE__, __METHOD__, $e->getMessage());
			}

			//トランザクションEnd
			$db->closeConnection();
		}
	}

	/**
	* 集計対象駅判定SQL
	*
	* @access	public
	* @param	array	パラメータ
	* @return	string	SQL
	*/
	public function generateSql($params, &$db_params)
	{
		$db_params['TgtStNm'] = $params['TgtStNm'];
		$db_params['TransferCount'] = $params['TransferCount'];

		$sql="";
		$sql .= "SELECT";
		$sql .= "        COUNT(*) CNT";
		$sql .= "    FROM";
		$sql .= "        T94COMMUTING_EKI_T03SUM";
		$sql .= "    WHERE";
		$sql .= "        T94_TARGET_EKI_NAME = :TgtStNm";
		$sql .= "        AND T94_TRANSFER_COUNT = :TransferCount ";

		return $sql;
	}

	/**
	* 沿線データ抽出SQL
	*
	* @access	public
	* @param	array	パラメータ
	* @param	array	ヴァル研究所データ
	* @return	string	SQL
	*/
	public function generateEnsenSelectSql($params,$valData,&$db_params)
	{
		// 駅コード取得
		$db_params['TransferCount']    = $params['TransferCount'];
		$db_params['EndRangeTimeTo']   = $params['EndRangeTimeTo'];
		$db_params['EndRangeTimeFrom'] = $params['EndRangeTimeFrom'];
		$db_params['stCd']             = substr($valData[2], 0, 5);
		
		$sql  ="";
		$sql  =" SELECT";
		$sql .="     M02.M02_ENSEN_CD T57_ENSEN_SHIKUCHOUSON_CD ,";
		$sql .="     M02.M02_ENSEN_NAME T57_ENSEN_SHIKUCHOUSON_NAME ,";
		$sql .="     NVL(M02.M02_ENSEN_KUBUN, 4) AS M02_ENSEN_KUBUN ,";
		$sql .="     M02.M02_ENSEN_KANA ,";
		$sql .="     NVL(SUM(T94_SUM), 0) AS DATACNT";
		$sql .=" FROM";
		$sql .="     (SELECT";
		$sql .="          T54.T54_ENSEN_CD AS T94_ENSEN_CD,";
		$sql .="          SUM(T54.T54_EKI_CNT) AS T94_SUM";
		$sql .="      FROM";
		$sql .="          T94COMMUTING_EKI_T03SUM T94,";
		$sql .="          T54EKI_SUM T54,";
		$sql .="          M03EKI M03";
		$sql .="      WHERE";
		$sql .="          T94.T94_TARGET_EKI_CD = :stCd AND";
		$sql .="          T94.T94_RENRAKU_EKI_CD IS NULL AND";
		$sql .="          T94.T94_TARGET_EKI_TIME between :EndRangeTimeFrom AND :EndRangeTimeTo AND";
		$sql .="          T94.T94_TRANSFER_COUNT = :TransferCount AND";
		$sql .="          T94.T94_EKI_CD = T54.T54_EKI_CD AND";
		$sql .="          T54.T54_DATA_KUBUN = 0 AND";
		$sql .="          T94.T94_EKI_CD = M03.M03_EKI_CD AND";
		$sql .="          M03.M03_RENRAKU_EKI_CD IS NULL";
		$sql .="      GROUP BY";
		$sql .="          T54.T54_ENSEN_CD";
		$sql .="     UNION ALL";
		$sql .="     SELECT /*+ index(M03 IDX_M03EKI_SEARCH03) */";
		$sql .="          T54.T54_ENSEN_CD AS T94_ENSEN_CD,";
		$sql .="          SUM(T54.T54_EKI_CNT) AS T94_SUM";
		$sql .="      FROM";
		$sql .="          T54EKI_SUM T54,";
		$sql .="          M03EKI M03,";
		$sql .="          (SELECT";
		$sql .="               T94.T94_RENRAKU_EKI_CD";
		$sql .="           FROM";
		$sql .="               T94COMMUTING_EKI_T03SUM T94";
		$sql .="           WHERE";
		$sql .="               T94.T94_TARGET_EKI_CD = :stCd AND";
		$sql .="               T94.T94_RENRAKU_EKI_CD IS NOT NULL AND";
		$sql .="               T94.T94_TARGET_EKI_TIME between :EndRangeTimeFrom AND :EndRangeTimeTo AND";
		$sql .="               T94.T94_TRANSFER_COUNT = :TransferCount";
		$sql .="           GROUP BY";
		$sql .="             T94.T94_RENRAKU_EKI_CD) T94";
		$sql .="      WHERE";
		$sql .="          M03.M03_RENRAKU_EKI_CD = T94.T94_RENRAKU_EKI_CD AND";
		$sql .="          M03.M03_EKI_CD = T54.T54_EKI_CD AND";
		$sql .="          T54.T54_DATA_KUBUN = 0";
		$sql .="      GROUP BY";
		$sql .="          T54.T54_ENSEN_CD) T94,";
		$sql .="     M02ENSEN M02";
		$sql .=" WHERE";
		$sql .="    T94.T94_ENSEN_CD = M02.M02_ENSEN_CD";
		$sql .=" GROUP BY";
		$sql .="     M02.M02_ENSEN_KUBUN,";
		$sql .="     M02.M02_ENSEN_KANA,";
		$sql .="     M02.M02_ENSEN_CD,";
		$sql .="     M02.M02_ENSEN_NAME";
		$sql .=" ORDER BY";
		$sql .="     M02.M02_ENSEN_KUBUN,";
		$sql .="     M02.M02_ENSEN_KANA";
		
/*

		$sql="";
		$sql .= "SELECT";
		$sql .= "        M02_ENSEN_CD T57_ENSEN_SHIKUCHOUSON_CD";			// 沿線・駅検索のテンプレート流用の為カラム名をそちらに併せています
		$sql .= "        ,M02_ENSEN_NAME T57_ENSEN_SHIKUCHOUSON_NAME";		// 沿線・駅検索のテンプレート流用の為カラム名をそちらに併せています
		$sql .= "        ,NVL(M02_ENSEN_KUBUN, 4) AS M02_ENSEN_KUBUN";
		$sql .= "        ,M02_ENSEN_KANA";
		$sql .= "        ,NVL(SUM(REC_CNT), 0) AS DATACNT";
		$sql .= "    FROM";
		$sql .= "        (";
		for($i=1; $i<=3; $i++){
			$sql .= "            SELECT";
			$sql .= "                    M02_ENSEN_CD";
			$sql .= "                    ,M02_ENSEN_NAME";
			$sql .= "                    ,M02_ENSEN_KUBUN";
			$sql .= "                    ,M02_ENSEN_KANA";
			$sql .= "                    ,REC_CNT";
			$sql .= "                FROM";
			$sql .= "                    M02ENSEN";
			$sql .= "                    ,(";
			$sql .= "                        SELECT";
			$sql .= "                                M03_ENSEN_CD";
			$sql .= "                                ,COUNT(T03_HEYA_CD) AS REC_CNT";
			$sql .= "                            FROM";
			$sql .= "                                T03WEB_HEYA";
			$sql .= "                                ,M03EKI";
			$sql .= "                                ,M05TENPO_INFO";
			$sql .= "                                ,(";
			$sql .= "                                    SELECT";
			$sql .= "                                            DISTINCT T94_RENRAKU_EKI_CD";
			$sql .= "                                            ,MIN(T94_TARGET_EKI_TIME) AS T94_TARGET_EKI_TIME";
			$sql .= "                                        FROM";
			$sql .= "                                            T94COMMUTING_EKI_T03SUM";
			$sql .= "                                        WHERE";
			$sql .= "                                            T94_TARGET_EKI_CD = :stCd ";
			$sql .= "                                            AND T94_RENRAKU_EKI_CD IS NOT NULL";
			$sql .= "                                            AND T94_TARGET_EKI_TIME > 0";
			$sql .= "                                            AND T94_TARGET_EKI_TIME <= :EndRangeTimeTo ";
			$sql .= "                                            AND T94_TRANSFER_COUNT  = :TransferCount ";
			$sql .= "                                        GROUP BY";
			$sql .= "                                            T94_RENRAKU_EKI_CD";
			$sql .= "                                )";
			$sql .= "                            WHERE";
			$sql .= "                                M03_RENRAKU_EKI_CD = T94_RENRAKU_EKI_CD";
			$sql .= "                                AND T03_RENRAKU_EKI_CD0".$i." = M03_RENRAKU_EKI_CD";
			$sql .= "                                AND T03_RENRAKU_EKI_CD0".$i." = T94_RENRAKU_EKI_CD";
			$sql .= "                                AND T03_RENRAKU_EKI_CD0".$i." IS NOT NULL";
			$sql .= "                                AND T94_TARGET_EKI_TIME <= :EndRangeTimeTo ";
			$sql .= "                                AND T94_TARGET_EKI_TIME >= :EndRangeTimeFrom ";
			$sql .= "                                AND M05_TENPO_CD = T03_TENPO_CD";
			$sql .= "                            GROUP BY";
			$sql .= "                                M03_ENSEN_CD";
			$sql .= "                    )";
			$sql .= "                WHERE";
			$sql .= "                    M02_ENSEN_CD = M03_ENSEN_CD";
			$sql .= "            UNION ALL";
			$sql .= "            SELECT";
			$sql .= "                    M02_ENSEN_CD";
			$sql .= "                    ,M02_ENSEN_NAME";
			$sql .= "                    ,M02_ENSEN_KUBUN";
			$sql .= "                    ,M02_ENSEN_KANA";
			$sql .= "                    ,COUNT(T03_HEYA_CD) AS REC_CNT";
			$sql .= "                FROM";
			$sql .= "                    T03WEB_HEYA";
			$sql .= "                    ,M02ENSEN";
			$sql .= "                    ,M05TENPO_INFO";
			$sql .= "                    ,(";
			$sql .= "                        SELECT";
			$sql .= "                                T94_EKI_CD";
			$sql .= "                                ,T94_TARGET_EKI_TIME";
			$sql .= "                            FROM";
			$sql .= "                                T94COMMUTING_EKI_T03SUM";
			$sql .= "                            WHERE";
			$sql .= "                                T94_TARGET_EKI_CD = :stCd ";
			$sql .= "                                AND T94_RENRAKU_EKI_CD IS NULL";
			$sql .= "                                AND T94_TARGET_EKI_TIME > 0";
			$sql .= "                                AND T94_TARGET_EKI_TIME <= :EndRangeTimeTo ";
			$sql .= "                                AND T94_TRANSFER_COUNT  = :TransferCount ";
			$sql .= "                    )";
			$sql .= "                WHERE";
			$sql .= "                    T03_EKI_CD0".$i." = T94_EKI_CD";
			$sql .= "                    AND T03_RENRAKU_EKI_CD0".$i." IS NULL";
			$sql .= "                    AND T94_TARGET_EKI_TIME <= :EndRangeTimeTo ";
			$sql .= "                    AND T94_TARGET_EKI_TIME >= :EndRangeTimeFrom ";
			$sql .= "                    AND T03_ENSEN_CD0".$i." = M02_ENSEN_CD";
			$sql .= "                    AND M05_TENPO_CD = T03_TENPO_CD";
			$sql .= "                GROUP BY";
			$sql .= "                    M02_ENSEN_KUBUN";
			$sql .= "                    ,M02_ENSEN_KANA";
			$sql .= "                    ,M02_ENSEN_CD";
			$sql .= "                    ,M02_ENSEN_NAME";
			if($i <> 3){
				$sql .= "            UNION ALL";
			}
		}
		$sql .= "        )";
		$sql .= "    GROUP BY";
		$sql .= "        M02_ENSEN_KUBUN";
		$sql .= "        ,M02_ENSEN_KANA";
		$sql .= "        ,M02_ENSEN_CD";
		$sql .= "        ,M02_ENSEN_NAME";
		$sql .= "    ORDER BY";
		$sql .= "        M02_ENSEN_KUBUN";
		$sql .= "        ,M02_ENSEN_KANA";
*/
		return $sql;
	}

	/**
	* T94COMMUTING_EKI_T03SUMへのデータインサート処理
	*
	* @access	public
	* @param	array	パラメータ
	* @param	array	ヴァル研究所データ
	* @return	string	SQL
	*/
	public function generateInsertSql($params,$valData)
	{
		// 駅コード取得
		$stCd = substr($valData[2], 0, 5);

		$sql="";
		$sql .= "INSERT INTO T94COMMUTING_EKI_T03SUM ";
		$sql .= "SELECT";
		$sql .= "        DISTINCT '".$stCd."' AS TARGET_EKI_CD";
		$sql .= "        ,'".$params["TgtStNm"]."' AS TARGET_EKI_NAME";
		$sql .= "        ,VALTIME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_RENRAKU_EKI_CD";
		$sql .= "        ,M03_ROSENZU_EKI_CD";
		$sql .= "        ,SYSDATE";
		$sql .= "        ,'".$params["TransferCount"]."' AS TRANSFER_COUNT ";
		$sql .= "FROM (";

		for ($i=0; $i<=intval($valData[1]-1); $i=$i+1)
		{
			$intRosenzucd=substr(trim($valData[$i+2]),0,5); //arrSrcは、3番目から駅CDが入っている
			if ($i==0){
				//i=0の時、目的地駅
				$valData[$i+2]=trim($valData[$i+2])."000";
			}
			if ($strRailRuteTime!=substr(trim($valData[$i+2]),strlen(trim($valData[$i+2]))-(4), 2)){
				//所要時間がひとつ前のと違う場合 right(Trim(arrSrc(i+2)),3)
				if ($i!=0){
					$sql .= " )  ";
					$sql .= "UNION ";
				}

				$sql .= "SELECT M03_ENSEN_CD,M03_EKI_CD,M03_RENRAKU_EKI_CD,M03_ROSENZU_EKI_CD, ";
				$sql .= "'".substr(trim($valData[$i+2]),strlen(trim($valData[$i+2]))-(4), 2)."' AS VALTIME ";
				$sql .= "FROM M03EKI WHERE ";
				$sql .= "M03_ROSENZU_EKI_CD IN (".$intRosenzucd;

			}
			else{
				$sql .= ",".$intRosenzucd;
			}
			$strRailRuteTime=substr(trim($valData[$i+2]),strlen(trim($valData[$i+2]))-(4), 2);

			if ($i==intval($valData[1]-1)){
				$sql .= " )";
			}
		}

		if (intval($valData[1])==0){
			$sql .= "SELECT M03_ENSEN_CD,M03_EKI_CD,M03_RENRAKU_EKI_CD,M03_ROSENZU_EKI_CD, ";
			$sql .= "'00' AS VALTIME ";
			$sql .= "FROM M03EKI WHERE ";
			$sql .= "M03_ROSENZU_EKI_CD = ".substr($valData[2],0,5);
		}

		$sql .= ") ";
		$sql .= "ORDER BY VALTIME ";

		return $sql;
	}
}
?>